package cn.zy.company.dao;

import cn.zy.company.entity.User;
import cn.zy.company.util.DBUtil;
import cn.zy.company.util.DataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    public void add(User user) throws Exception {
        Connection connection = DataSource.getConnection();
        DBUtil.insert(connection, user);
        DataSource.closeConnection(connection);
    }

    public void delete(Integer id) throws Exception {
        Connection connection = DataSource.getConnection();
        DBUtil.delete(connection, id, User.class);
        DataSource.closeConnection(connection);
    }

    public void update(User user) throws Exception {
        Connection connection = DataSource.getConnection();
        DBUtil.update(connection, user);
        DataSource.closeConnection(connection);
    }

    public User findById(Integer id) throws Exception {
        Connection connection = DataSource.getConnection();
        User user = DBUtil.selectOne(connection, id, User.class);
        return user;
    }

    public List<User> findAll() throws Exception {
        Connection connection = DataSource.getConnection();
        List<User> list = DBUtil.select(connection, "", new Object[]{}, User.class);
        DataSource.closeConnection(connection);
        return list;
    }


    public User find(String username, String password) throws Exception {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT * FROM USER WHERE username=? AND password=?");
        statement.setObject(1, username);
        statement.setObject(2, password);
        ResultSet resultSet = statement.executeQuery();
        User user = null;
        while (resultSet.next()) {
            user = new User(resultSet.getString("username"), resultSet.getString("password"));
            user.setId(resultSet.getInt("id"));

        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return user;

    }
    public long countAll() throws SQLException {
        Connection connection = DataSource.getConnection();
        long count = DBUtil.count(connection, "", new Object[]{}, User.class);
        DataSource.closeConnection(connection);
        return count;

    }

    public List<User> findAll(Integer page, Integer size) throws SQLException {
        List<User> list=new ArrayList<User>();
        User user=null;
        Connection connection = DataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from user  limit ?,?");
        preparedStatement.setObject(1,(page-1)*size);
        preparedStatement.setObject(2,size);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            user=new User(resultSet.getString("username"),resultSet.getNString("password"));
            user.setId(resultSet.getInt("id"));
            list.add(user);
        }
        resultSet.close();
        preparedStatement.close();
        DataSource.closeConnection(connection);
        return list;
    }
    public List<User> find(String username, Integer page, Integer size) throws SQLException {
        List<User> list=new ArrayList<User>();
        User user=null;
        Connection connection = DataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from user where username like ? limit ?,?");
        preparedStatement.setObject(1,"%" + username + "%");
        preparedStatement.setObject(2,(page-1)*size);
        preparedStatement.setObject(3,size);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            user=new User(resultSet.getString("username"),resultSet.getNString("password"));
            user.setId(resultSet.getInt("id"));
            list.add(user);
        }
        resultSet.close();
        preparedStatement.close();
        DataSource.closeConnection(connection);
        return list;

    }
}
